package com.hymjweb.demo.hymj.master_bill_manage.load.mapper;

import com.hymjweb.demo.framework.util.DataTypeUtil;
import com.hymjweb.demo.framework.util.DateUtil;
import com.hymjweb.demo.hymj.master_bill_manage.load.bean.BNativeBillLoadPojo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Component;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
@Component
public interface BNativeBillLoadMapper {



    class BNativeBillLoadMapperProvider{


        /**
         * t1.native_bill_id, t1.master_bill_id, t.attribution_department_id, t.open_bill_date, t.open_bill_department_id,
         *        t1.flight_name, t.airport_id, t1.airlines_id, t.dest_city_id, t.goods_name,
         *        t1.load_number, t1.load_actual_weight, t1.load_chargeable_weight,
         *        t1.load_volume, t1.share_transport_cost_amount, t1.share_ground_amount,
         *        t1.share_airlines_add_cost_amount, t1.share_other_charges_amount, t1.share_total_cost_amount,
         *        t1.share_gross_profit, t1.share_total_cost_comment,
         *        t.delivery_customer_name, t.delivery_customer_phone, t.delivery_customer_address
         *
         *
         *           t.total_number, t.actual_weight, t.chargeable_weight,
         * @param map
         * @return
         */

        public String getNoPageList(Map map){
            String sql= new SQL() {{
                SELECT( "t.open_bill_date, t.id, t.bill_load_master_bill_ids, t.airport_id , t1.flight_name, t.dest_city_id, t.goods_name, t.total_number, t.actual_weight, t.chargeable_weight, t.settlement_amount, t.open_bill_delivery_charge, t.agent_delivery_charge, t.service_charge, t.share_cost, t1.load_number, t1.load_actual_weight, t1.load_chargeable_weight, t1.load_volume, t1.share_transport_cost_amount, t1.share_ground_amount, t1.share_airlines_add_cost_amount, t1.share_other_charges_amount, t1.share_total_cost_amount, t.share_cost, t.gross_profit, t.airlines_id, t.delivery_customer_name, t.delivery_customer_saleman_id, t1.share_total_cost_comment, t.share_cost_comment, ifnull(t2.pull_cost_share_amount,0) pull_cost_share_amount, t.share_cost + ifnull(t2.pull_cost_share_amount,0) total_share_cost, t.settlement_amount - (t.share_cost + ifnull(t2.pull_cost_share_amount, 0)) settlement_gross_profit");
                FROM("b_native_bill_load t1 LEFT JOIN b_native_bill t ON t1.native_bill_id = t.id left join (select t2.native_bill_id, sum(t2.cost_share_amount) pull_cost_share_amount from b_native_bill_pull_cost t2 group by t2.native_bill_id ) t2 on t1.native_bill_id = t2.native_bill_id");
                WHERE("1=1 and t.cancel_status_id=411");  // 411 为 未作废 状态
            }}.toString();

            if(map!=null){
                String keyBillCode=DataTypeUtil.toStringObject(map.get("keyBillCode"));
                String keyAttributionDepartmentId = DataTypeUtil.toStringObject(map.get("keyAttributionDepartmentId"));
                Long keyTransportSpeciesId = DataTypeUtil.toLongObject(map.get("keyTransportSpeciesId"));
                Long keyTransportWayId = DataTypeUtil.toLongObject(map.get("keyTransportWayId"));
                String keyDeliveryCustomerId = DataTypeUtil.toNotNullString(map.get("keyDeliveryCustomerId"));
                String keyReceiveCustomerName = DataTypeUtil.toNotNullString(map.get("keyReceiveCustomerName"));
                String keyPickUpPointId = DataTypeUtil.toNotNullString(map.get("keyPickUpPointId"));
                String keyOpenBillStartDate = DataTypeUtil.toNotNullString(map.get("keyOpenBillStartDate"));
                String keyOpenBillEndDate = DataTypeUtil.toNotNullString(map.get("keyOpenBillEndDate"));

                if(keyBillCode!=null && !"".equals(keyBillCode)){
                    sql += " and t.id=" + DataTypeUtil.quoteStr(keyBillCode);
                }

                if(keyAttributionDepartmentId!=null){
                    sql += " and t.attribution_department_id = " + DataTypeUtil.quoteStr(keyAttributionDepartmentId);
                }

                if(keyTransportSpeciesId!=null){
                    sql += " and t.transport_species_id=" + keyTransportSpeciesId;
                }
                if(keyTransportWayId!=null){
                    sql += " and t.transport_way_id=" + keyTransportWayId;
                }

                if(!"".equals(keyDeliveryCustomerId)){
                    sql += " and t.delivery_customer_id = " + DataTypeUtil.quoteStr(keyDeliveryCustomerId);
                }

                if(!"".equals(keyReceiveCustomerName)){
                    sql += " and t.receive_customer_name like " + DataTypeUtil.quoteAllLikeStr(keyReceiveCustomerName);
                }

                if(!"".equals(keyPickUpPointId)){
                    sql += " and (t.pick_up_point_id = " + DataTypeUtil.quoteStr(keyPickUpPointId) + " or t.pick_up_point_name like " + DataTypeUtil.quoteAllLikeStr(keyPickUpPointId) + ")";
                }


                if(!"".equals(keyOpenBillStartDate)){
                    if(!"".equals(keyOpenBillEndDate)){
                        int d=keyOpenBillStartDate.compareTo(keyOpenBillEndDate);
                        if(d>0){
                            String tempDate = keyOpenBillStartDate;
                            keyOpenBillStartDate = keyOpenBillEndDate;
                            keyOpenBillEndDate = tempDate;
                        }
                        sql += " and (date_format(t.create_time,'%Y-%m-%d') between "+ DataTypeUtil.quoteStr(keyOpenBillStartDate) +" and " + DataTypeUtil.quoteStr(keyOpenBillEndDate) + ")";
                    }
                    else{
                        sql += " and date_format(t.create_time,'%Y-%m-%d') >= "+ DataTypeUtil.quoteStr(keyOpenBillStartDate);
                    }
                }
                else{
                    if(!"".equals(keyOpenBillEndDate)){
                        sql += " and date_format(t.create_time,'%Y-%m-%d') <= "+ DataTypeUtil.quoteStr(keyOpenBillEndDate);
                    }
                }


            }

            return sql;
        }

        public String queryBNativeBillLoadPojoByMapParam(Map param){
            String sql= new SQL() {{
                SELECT("t.* ");
                FROM(" b_native_bill_load t");
                WHERE("1=1");
            }}.toString();

            /**
             *   param.put("masterBillId", pojo.getMasterBillId());
             *             param.put("masterBillTypeId", pojo.getMasterBillTypeId());
             *             param.put("nativeBillId", pojo.getNativeBillId());
             *             param.put("flightName", pojo.getFlightName());
             *             master_bill_id,master_bill_type_id,native_bill_id,flight_name
             */
            if(param!=null){
                String masterBillId = DataTypeUtil.toStringObject(param.get("masterBillId"));
                Long masterBillTypeId = DataTypeUtil.toLongObject(DataTypeUtil.toStringObject(param.get("masterBillTypeId")));
                String nativeBillId =DataTypeUtil.toStringObject(param.get("nativeBillId"));
                String flightName = DataTypeUtil.toStringObject(param.get("flightName"));
                String flightDate = DateUtil.dateToStr_yyyy_MM_dd((Date)param.get("flightDate"));

                if(masterBillId!=null && masterBillTypeId!=null && nativeBillId!=null && flightName!=null && flightDate!=null){
                    sql += " and t.master_bill_id = " + DataTypeUtil.quoteStr(masterBillId);
                    sql += " and t.master_bill_type_id = " + DataTypeUtil.toLongObject(masterBillTypeId);
                    sql += " and t.native_bill_id = " + DataTypeUtil.quoteStr(nativeBillId);
                    sql += " and t.flight_name= " + DataTypeUtil.quoteStr(flightName);
                    sql += " and date_format(t.flight_date,'%Y-%m-%d') =" + DataTypeUtil.quoteStr(flightDate);
                }
            }

            return sql;
        }

        public String queryNativeBillListByInMasterBillId(String masterBillIds){
            String sql= new SQL() {{
                SELECT("t.* ");
                FROM(" b_native_bill_load t");
                WHERE("1=1 and t.master_bill_id in (" + masterBillIds + ")");
            }}.toString();
            return sql;
        }


        public String queryBNativeBillLoadPojoById(String id){
            String sql= new SQL() {{
                SELECT("t.* ");
                FROM(" b_native_bill_load t");
                WHERE("t.id = #{id}");
            }}.toString();
            return sql;
        }


        public String queryNativeBillListByMasterBillId(String masterBillId){
            String sql= new SQL() {{
                SELECT("t.* ");
                FROM(" b_native_bill_load t");
                WHERE("1=1 and t.master_bill_id = #{masterBillId}");
            }}.toString();
            return sql;
        }


        public String queryNativeBillLoadPojoByMasterBillIdAndMasterBillTypeIdAndNativeBillId(String masterBillId, Long masterBillTypeId, String nativeBillId){
            String sql= new SQL() {{
                SELECT("t.* ");
                FROM(" b_native_bill_load t");
                WHERE("1=1 and t.master_bill_id = #{masterBillId} and t.master_bill_type_id = #{masterBillTypeId} and t.native_bill_id = #{nativeBillId}");
            }}.toString();
            return sql;
        }


        public String findAllLoad(Map param){
            String sql= new SQL() {{
                SELECT("t.* ");
                FROM(" b_native_bill_load t ");
                WHERE("1=1");
            }}.toString();

            if(param!=null){
                String masterBillId = DataTypeUtil.toStringObject(param.get("masterBillId"));
                if(masterBillId!=null){
                    sql += " and t.master_bill_id = " + DataTypeUtil.quoteStr(masterBillId);
                }

                Long masterBillTypeId = DataTypeUtil.toLongObject(param.get("masterBillTypeId"));
                if(masterBillTypeId!=null){
                    sql += " and t.master_bill_type_id = " + masterBillTypeId;
                }

                String billId = DataTypeUtil.toStringObject(param.get("billId"));
                if(billId!=null){
                    sql += " and t.native_bill_id = " + DataTypeUtil.quoteStr(billId);
                }

                String excludeMasterBillIds = DataTypeUtil.toStringObject(param.get("excludeMasterBillIds"));
                if(excludeMasterBillIds!=null && excludeMasterBillIds.length()>0){
                    String[] excludeMasterBillIdArray = excludeMasterBillIds.split(",");
                    String tempMids = "";
                    for(String mid : excludeMasterBillIdArray){
                        tempMids += DataTypeUtil.quoteStr(mid) + ",";
                    }
                    tempMids = tempMids.substring(0,tempMids.length()-1);
                    sql += " and t.master_bill_id not in (" + tempMids + ") ";
                }

            }

            return sql;
        }

        public String deleteLoadNativeBill(String masterBillId){
            String sql = new SQL() {{
                DELETE_FROM("b_native_bill_load t");
                WHERE("t.master_bill_id = #{masterBillId}");
            }}.toString();
            return sql;
        }


        public String delete(String id){
            String sql = new SQL() {{
                DELETE_FROM("b_native_bill_load t");
                WHERE("t.id = #{id}");
            }}.toString();
            return sql;
        }

        public String getLoadTotalNativeMap(String billId){
            String sql = new SQL() {{
                SELECT("t.native_bill_id, sum(ifnull(t.load_number,0)) total_load_number, sum(ifnull(t.load_actual_weight,0)) total_load_actual_weight, sum(ifnull(t.load_chargeable_weight,0)) total_load_chargeable_weight, sum(ifnull(t.load_volume,0)) total_load_volume, sum(ifnull(t.share_transport_cost_amount,0)) share_transport_cost_amount, sum(ifnull(t.share_ground_amount,0)) share_ground_amount, sum(ifnull(t.share_airlines_add_cost_amount,0)) share_airlines_add_cost_amount,sum(ifnull(t.share_other_charges_amount,0)) share_other_charges_amount, sum(ifnull(t.share_total_cost_amount,0)) share_total_cost_amount ");
                FROM("b_native_bill_load t ");
                WHERE(" 1 = 1 AND t.native_bill_id = " + DataTypeUtil.quoteStr(billId) );  // 353代表该分单已经全部配载完成
                GROUP_BY(" t.native_bill_id");
            }}.toString();

            return  sql;
        }


        public String update(BNativeBillLoadPojo pojo){
            String sql= new SQL() {{
                UPDATE("b_native_bill_load t");
                SET("t.master_bill_id = #{masterBillId}");
                SET("t.master_bill_type_id = #{masterBillTypeId}");
                SET("t.native_bill_id = #{nativeBillId}");
                SET("t.airlines_id = #{airlinesId}");
                SET("t.flight_no = #{flightNo}");
                SET("t.flight_name = #{flightName}");
                SET("t.flight_date = #{flightDate}");
                SET("t.load_number = #{loadNumber}");
                SET("t.load_actual_weight = #{loadActualWeight}");
                SET("t.load_chargeable_weight = #{loadChargeableWeight}");
                SET("t.load_volume = #{loadVolume}");
                SET("t.share_transport_cost_amount = #{shareTransportCostAmount}");
                SET("t.share_ground_amount = #{shareGroundAmount}");
                SET("t.share_airlines_add_cost_amount = #{shareAirlinesAddCostAmount}");
                SET("t.share_other_charges_amount = #{shareOtherChargesAmount}");
                SET("t.share_total_cost_amount = #{shareTotalCostAmount}");
                SET("t.share_total_cost_comment = #{shareTotalCostComment}");
                WHERE("t.id = #{id}");
            }}.toString();
            return sql;
        }



        public String insert(BNativeBillLoadPojo pojo){
            String sql = new SQL() {{
                INSERT_INTO("b_native_bill_load");
                VALUES("id","#{id}");
                VALUES("master_bill_id","#{masterBillId}");
                VALUES("master_bill_type_id","#{masterBillTypeId}");
                VALUES("native_bill_id","#{nativeBillId}");
                VALUES("airlines_id","#{airlinesId}");
                VALUES("flight_no","#{flightNo}");
                VALUES("flight_name","#{flightName}");
                VALUES("flight_date","#{flightDate}");
                VALUES("load_number","#{loadNumber}");
                VALUES("load_actual_weight","#{loadActualWeight}");
                VALUES("load_chargeable_weight","#{loadChargeableWeight}");
                VALUES("load_volume","#{loadVolume}");
                VALUES("share_transport_cost_amount","#{shareTransportCostAmount}");
                VALUES("share_ground_amount","#{shareGroundAmount}");
                VALUES("share_airlines_add_cost_amount","#{shareAirlinesAddCostAmount}");
                VALUES("share_other_charges_amount","#{shareOtherChargesAmount}");
                VALUES("share_total_cost_amount","#{shareTotalCostAmount}");
                VALUES("share_total_cost_comment","#{shareTotalCostComment}");
            }}.toString();
            return sql;
        }
    }

    @Results(id = "nativeBillLoadFlightMapId",value = {
            @Result(column = "id", property = "id"),
            @Result(column = "master_bill_id", property = "masterBillId"),
            @Result(column = "master_bill_type_id", property = "masterBillTypeId"),
            @Result(column = "native_bill_id", property = "nativeBillId"),
            @Result(column = "airlines_id", property = "airlinesId"),
            @Result(column = "flight_no", property = "flightNo"),
            @Result(column = "flight_name", property = "flightName"),
            @Result(column = "flight_date", property = "flightDate"),
            @Result(column = "load_number", property = "loadNumber"),
            @Result(column = "load_actual_weight", property = "loadActualWeight"),
            @Result(column = "load_chargeable_weight", property = "loadChargeableWeight"),
            @Result(column = "load_volume", property = "loadVolume"),
            @Result(column = "share_transport_cost_amount", property = "shareTransportCostAmount"),
            @Result(column = "share_ground_amount", property = "shareGroundAmount"),
            @Result(column = "share_airlines_add_cost_amount", property = "shareAirlinesAddCostAmount"),
            @Result(column = "share_other_charges_amount", property = "shareOtherChargesAmount"),
            @Result(column = "share_total_cost_amount", property = "shareTotalCostAmount"),
            @Result(column = "share_total_cost_comment", property = "shareTotalCostComment")
    })
    @SelectProvider(type = BNativeBillLoadMapperProvider.class, method = "findAllLoad")
    List<BNativeBillLoadPojo> findAllLoad(Map param);


    @ResultMap("nativeBillLoadFlightMapId")
    @SelectProvider(type = BNativeBillLoadMapperProvider.class, method = "queryNativeBillListByInMasterBillId")
    List<BNativeBillLoadPojo> queryNativeBillListByInMasterBillId(String masterBillIds);

    @ResultMap("nativeBillLoadFlightMapId")
    @SelectProvider(type = BNativeBillLoadMapperProvider.class, method = "queryNativeBillListByMasterBillId")
    List<BNativeBillLoadPojo> queryNativeBillListByMasterBillId(String masterBillId);


    @ResultType(Map.class)
    @SelectProvider(type = BNativeBillLoadMapperProvider.class, method = "getLoadTotalNativeMap")
    Map<String, Object> getLoadTotalNativeMap(String id);

    @ResultMap("nativeBillLoadFlightMapId")
    @SelectProvider(type = BNativeBillLoadMapperProvider.class, method = "queryBNativeBillLoadPojoByMapParam")
    BNativeBillLoadPojo queryBNativeBillLoadPojoByMapParam(Map<String, Object> param);


    @ResultType(Map.class)
    @SelectProvider(type = BNativeBillLoadMapperProvider.class, method = "getNoPageList")
    List<Map<String, Object>> getNoPageList(Map map);

    @ResultMap("nativeBillLoadFlightMapId")
    @SelectProvider(type = BNativeBillLoadMapperProvider.class, method = "queryBNativeBillLoadPojoById")
    BNativeBillLoadPojo queryBNativeBillLoadPojoById(String id);

    /**
     * 配载分单批量插入
     * @param bNativeBillLoadPojos 航空主单包含的分单开列表   // SET("t.share_total_cost_comment = #{shareTotalCostComment}");
     * @return 插入行数
     */
    @Insert(
            "<script>" +
                    "insert into b_native_bill_load(id, master_bill_id, master_bill_type_id, native_bill_id, airlines_id, flight_no, flight_name, flight_date, load_number, load_actual_weight, load_chargeable_weight, load_volume, share_transport_cost_amount, share_ground_amount, share_airlines_add_cost_amount,share_other_charges_amount, share_total_cost_amount, share_total_cost_comment) values " +
                    "<foreach collection='bNativeBillLoadPojos' item='pojo' separator=','>" +
                    "(#{pojo.id},#{pojo.masterBillId},#{pojo.masterBillTypeId},#{pojo.nativeBillId},#{pojo.airlinesId},#{pojo.flightNo},#{pojo.flightName},#{pojo.flightDate},#{pojo.loadNumber},#{pojo.loadActualWeight},#{pojo.loadChargeableWeight},#{pojo.loadVolume}, #{pojo.shareTransportCostAmount}, #{pojo.shareGroundAmount}, #{pojo.shareAirlinesAddCostAmount}, #{pojo.shareOtherChargesAmount}, #{pojo.shareTotalCostAmount}, #{pojo.shareTotalCostComment})" +
                    "</foreach>" +
            "</script>"
    )
    @Options(keyProperty = "id")
    int batchInsertLoadBill(@Param("bNativeBillLoadPojos") List<BNativeBillLoadPojo> bNativeBillLoadPojos);


    @InsertProvider(type = BNativeBillLoadMapperProvider.class, method = "insert")
    @Options(keyProperty = "id")
    int insert(BNativeBillLoadPojo pojo);


    /**
     * 更新配载表
     * @param pojo
     * @return
     */
    @UpdateProvider(type = BNativeBillLoadMapperProvider.class, method = "update")
    int update(BNativeBillLoadPojo pojo);

    @DeleteProvider(type = BNativeBillLoadMapperProvider.class, method = "deleteLoadNativeBill")
    int deleteLoadNativeBill(String masterBillId);

    @DeleteProvider(type = BNativeBillLoadMapperProvider.class, method = "delete")
    int delete(String id);


}
